package com.xiaobawang.homework1225.mapper;

import com.xiaobawang.homework1225.entity.Role;
import com.xiaobawang.homework1225.entity.RoleInfo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * description：User Controller
 * author：Synhard
 * create time：2019/12/25 15：48
 * last modified: 2019/12/25 15：48
 * **/
@Mapper
@Repository
public interface RoleMapper {

    @Select("SELECT * FROM role WHERE r_name = #{r_name}")
    Role selectRoleByName(String r_name);

    @Insert("INSERT INTO role(r_name, r_remark) VALUES (#{r_name}, #{r_remark})")
    int insertRole(String r_name, String r_remark);

    @Select("SELECT COUNT(1) FROM userrolerelation WHERE role_id = #{r_id}")
    int selectUserUnderRole(int r_id);

    @Delete("DELETE FROM role WHERE r_id = #{r_id}")
    int deleteRoleById(int r_id);

    @Select("SELECT * FROM role WHERE r_name like '%${r_name}%'")
    List<Role> fuzzySelectRoleByName(String r_name);

    @Select("SELECT role.r_name, count(userrolerelation.user_id) as u_number FROM role INNER JOIN userrolerelation ON (role.r_id = userrolerelation.role_id) GROUP BY (role.r_name)")
    List<RoleInfo> selectRoleInfo();
}
